import re
import paramiko
import os
import pyodbc
from pyexcel_xls import *
import sftp_config
from time import strftime, localtime


class Sftp(object):
    def __init__(self):
        self.host = "b2bo2oautosql01.database.chinacloudapi.cn"
        self.user = "pgadmin"
        self.pwd = "6ekVH4pmeq$Pn1xq"
        self.db = "o2oauto_prd"
        host = sftp_config.host
        port = sftp_config.port
        username = sftp_config.username
        password = sftp_config.password
        sf = paramiko.Transport((host, port))
        sf.connect(username=username, password=password)
        self.sftp = paramiko.SFTPClient.from_transport(sf)
        self.local = 'F:\\0'  # 本地文件
        self.remote = '/test'  # 远程文件或目录

    def __GetConnect(self):  # 得到数据库连接信息函数， 返回: conn.cursor()
        self.conn = pyodbc.connect(r'DRIVER={SQL Server Native Client 11.0};'
                                   r'SERVER=b2bo2oautosql01.database.chinacloudapi.cn;'
                                   r'DATABASE=o2oauto_prd;'
                                   r'UID=pgadmin;'
                                   r'PWD=6ekVH4pmeq$Pn1xq;'
                                   )
        cur = self.conn.cursor()  # 将数据库连接信息，赋值给cur。mssql_helper
        if not cur:
            return NameError, "连接数据库失败"
        else:
            return cur

    # 执行查询语句,返回的是一个包含tuple的list，list的元素是记录行，tuple的元素connect是每行记录的字段
    def ExecQuery(self, sql):  # 执行Sql语句函数，返回结果
        cur = self.__GetConnect()  # 获得数据库连接信息
        cur.execute(sql)  # 执行Sql语句
        resList = cur.fetchall()  # 获得所有的查询结果
        # 查询完毕后必须关闭连接
        self.conn.close()  # 返回查询结果
        return resList

    def DownLoadFile(self, sftp, LocalFile, RemoteFile):  # 下载单个文件
        file_handler = open(LocalFile, 'wb')
        sftp.get(RemoteFile, LocalFile)  # 下载目录中文件
        file_handler.close()

    def list_dir(self, sftp, LocalDir, RemoteDir):
        for file in sftp.listdir(RemoteDir):
            time = re.findall(r"\d{8}", file)
            now = int(strftime("%Y%m%d", localtime()))
            # now = int(strftime("%Y%m%d", localtime()))-1
            if int(time[0]) == now:
                Local = os.path.join(LocalDir, file)
                Remote = os.path.join(RemoteDir, file)
                if file.find(".") == -1:  # 判断是否是文件
                    if not os.path.exists(Local):
                        os.makedirs(Local)
                    self.DownLoadFileTree(sftp, Local, Remote)
                else:  # 文件
                    self.DownLoadFile(sftp, Local, Remote)
                # self.read_data(Local)
                # print(Local)

    def is_sava(self):
        sql1 = 'SELECT convert(varchar(10),createat,120) from oto_rt_couponsend_detail_auto GROUP BY createat'
        sql2 = 'SELECT convert(varchar(10),createat,120) from oto_rt_coupontrade_detail_auto GROUP BY createat'
        data1 = self.ExecQuery(sql1)
        data2 = self.ExecQuery(sql2)
        return data1, data2

    def parse_data(self, data):
        a = 0
        for i in data:
            if a != 0:
                for b in i:
                    print(b, end=' ')
                print()
            a += 1

    def read_data(self):
        area = r'{}\pg_rt_wm_from_zex_{}.xlsx'.format(self.local, str(strftime("%Y%m%d", localtime())))
        print(area)
        xls_data = get_data(area)
        # print("Get data type:", type(xls_data))
        for sheet_n in xls_data.keys():
            # print(sheet_n, ":", xls_data[sheet_n])
            self.parse_data(xls_data[sheet_n])

    def Main(self):
        self.list_dir(self.sftp, self.local, self.remote)
        # cur1, cur2 = self.is_sava()
        # print(type(cur1[0][0]), cur2[0][0])
        # for i in cur2:
        #     if i[0] == strftime("%Y-%m-%d", localtime())-1:
        #         print('已有存在的数据')
        #     else:
        #         print('y')


if __name__ == '__main__':
    sftp = Sftp()
    # sftp.Main()
    sftp.read_data()


